/**
 * 
 */
package com.feib.stms.service.impl;

import java.io.File;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Map;
import java.util.UUID;

import javax.sql.DataSource;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcOperations;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import com.feib.stms.dao.jdbc.SqlQueryMapProvider;
import com.feib.stms.util.UserUtil;

/**
 * @author Jimmy Liu
 *
 */
public class DeliveryPaymentChkSumExcelExportor extends BasePaymentFileContentExcelExportor
{

	public DeliveryPaymentChkSumExcelExportor(DataSource dataSource,
			SqlQueryMapProvider sqlQueryMapProvider, String[] headers,
			String temporaryBaseDirectory, String sheetName) {
		super(dataSource, sqlQueryMapProvider, headers, temporaryBaseDirectory,
				sheetName);
	}
	
	public String export() throws Exception
	{
		if (StringUtils.isEmpty(fileName))
			fileName = this.sheetName;

		if (StringUtils.isEmpty(titleName))
			titleName = this.sheetName;
		
		Workbook wb = new HSSFWorkbook(); // 若要產生xlsx則使用 XSSFWorkbook 即可

		Map<String, CellStyle> styles = createStyles(wb);

		Sheet sheet = wb.createSheet(sheetName);

		PrintSetup printSetup = sheet.getPrintSetup();
		printSetup.setLandscape(true);
		sheet.setFitToPage(true);
		sheet.setHorizontallyCenter(true);

		// 行數
		int rownum = 0;

		// title row
		// 創建一行並放一些單元格到該行中，行的索引是以0開始的
		Row titleRow = sheet.createRow(rownum++);
		titleRow.setHeightInPoints(33);
		Cell titleCell = titleRow.createCell(0);
		titleCell.setCellValue(titleName);
		titleCell.setCellStyle(styles.get("title"));
		sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$G$1")); //7 cell


		// printDate row
		// 證券業者
		Row printDateRow = sheet.createRow(rownum++);
		printDateRow.setHeightInPoints((float) 22.5);
		Cell securitiesCell = printDateRow.createCell(0);
		securitiesCell.setCellValue("證券業者：" + "亞東證券");
		securitiesCell.setCellStyle(styles.get("title2"));
		sheet.addMergedRegion(CellRangeAddress.valueOf("$A$2:$C$2"));
		//列印日期
		Cell printDateCell = printDateRow.createCell(3);        
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
		printDateCell.setCellValue("製表日期/時間：" + dateFormat.format(Calendar.getInstance().getTime()));
		printDateCell.setCellStyle(styles.get("title2"));
		sheet.addMergedRegion(CellRangeAddress.valueOf("$D$2:$G$2"));

		//condition row;
		Row conditionRow = sheet.createRow(rownum++);
		conditionRow.setHeightInPoints((float) 22.5);
		/* 暫不用 */


		// header row
		Row headerRow = sheet.createRow(rownum++);
		headerRow.setHeightInPoints((float) 34.5);
		Cell headerCell;
		for (int i = 0; i < headers.length; i++) {
			headerCell = headerRow.createCell(i);
			headerCell.setCellValue(headers[i]);
			headerCell.setCellStyle(styles.get("header"));
		}

		DeliveryPaymentChkSumXlsResultSetExtractor extrator = new DeliveryPaymentChkSumXlsResultSetExtractor(sheet, rownum, styles);

		NamedParameterJdbcOperations namedParameterJdbcOperations = new NamedParameterJdbcTemplate(dataSource);
		namedParameterJdbcOperations.query(sqlQueryMapProvider.getSql(), sqlQueryMapProvider.getParameters(), extrator);

		rownum = extrator.getRownum(); // 取得資料輸出後，row 所在的位置

		// 調整欄位寬度 adjust the column widths
		for (int i = 0; i < headers.length; i++) {
			sheet.autoSizeColumn((short) i,true);
		}

		// 輸出到硬碟
		String tempDownloadFilePath = temporaryBaseDirectory + separator + "excel" + separator + UserUtil.getUserGroup().getGroupNo() + separator + UserUtil.getUserId() + separator;

		logger.debug("[{}]暫存目錄:{}", sheetName, tempDownloadFilePath);

		File directory = new File(tempDownloadFilePath);
		if(!directory.exists())
		{
			FileUtils.forceMkdir(directory);
		}

		String fileFullName = tempDownloadFilePath + UUID.randomUUID().toString() + "-" + fileName + ".xls";

		FileOutputStream out;
		out = new FileOutputStream(fileFullName);
		wb.write(out);
		out.flush();
		out.close();
		
		return fileFullName;
	}

}
